/*
* Copyright (C) 2012 Manuel Pérez
*
* This file is part of OpenSudoku.
*
* OpenSudoku is free software: you can redistribute it and/or modify
* it under the terms of the GNU General Public License as published by
* the Free Software Foundation, either version 3 of the License, or
* (at your option) any later version.
*
* OpenSudoku is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
* GNU General Public License for more details.
*
* You should have received a copy of the GNU General Public License
* along with OpenSudoku. If not, see <http://www.gnu.org/licenses/>.
*
*/
package dBasea;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import modeloa.Abokatu;
import modeloa.BalorazioKotxeIstripu;
import modeloa.Bezero;
import modeloa.BezeroIbilgailu;
import modeloa.Diagnostiko;
import modeloa.Inbolukratu;
import modeloa.Istripua;
import modeloa.MedikuKlinika;
import modeloa.Peritu;
import modeloa.Tailer;

public class Kontsultak {

	public List<Bezero> listatuBezeroak() throws SQLException {
		DatuBasea dbk = DatuBasea.getInstance();
		String query = "select b.bezeroId,b.izena,b.abizenak,b.helbidea,b.telefonoa,a.deskripzioa FROM BezeroAseguratua b, AseguruTipoa a WHERE b.aseguruTipoKodigoa = a.kodigoa";
		ResultSet rs = dbk.executeSQL(query);
		Bezero beze;
		List<Bezero> lista = new ArrayList<Bezero>();
		while (rs.next()) {
			beze = new Bezero(rs.getString(1), rs.getString(2),
					rs.getString(3), rs.getString(4), rs.getInt(5),
					rs.getString(6));
			lista.add(beze);
		}
		rs.close();

		return lista;
	}

	public List<BezeroIbilgailu> listatuKotxeak() throws SQLException {
		DatuBasea dbk = DatuBasea.getInstance();
		String query = "select * FROM BezeroIbilgailuak ";
		ResultSet rs = dbk.executeSQL(query);
		BezeroIbilgailu beze;
		List<BezeroIbilgailu> lista = new ArrayList<BezeroIbilgailu>();
		while (rs.next()) {
			beze = new BezeroIbilgailu(rs.getString("matrikula"),
					rs.getString("bezeroId"), rs.getLong("urtea"));
			lista.add(beze);
		}
		rs.close();

		return lista;
	}

	public List<Bezero> bilatuBezeroak(int filtroa, String bila)
			throws SQLException {
		DatuBasea dbk = DatuBasea.getInstance();
		List<Bezero> lista = new ArrayList<Bezero>();
		if (filtroa == 1) {// NAN

			String query = "select b.bezeroId,b.izena,b.abizenak,b.helbidea,b.telefonoa,a.deskripzioa FROM BezeroAseguratua b, AseguruTipoa a WHERE b.aseguruTipoKodigoa = a.kodigoa AND b.bezeroId='"
					+ bila + "'";
			ResultSet rs = dbk.executeSQL(query);
			Bezero beze;
			while (rs.next()) {
				beze = new Bezero(rs.getString(1), rs.getString(2),
						rs.getString(3), rs.getString(4), rs.getInt(5),
						rs.getString(6));
				lista.add(beze);
			}
		} else if (filtroa == 2) // izena
		{
			String query = "select b.bezeroId,b.izena,b.abizenak,b.helbidea,b.telefonoa,a.deskripzioa FROM BezeroAseguratua b, AseguruTipoa a WHERE b.aseguruTipoKodigoa = a.kodigoa AND b.izena='"
					+ bila + "'";
			ResultSet rs = dbk.executeSQL(query);
			Bezero beze;
			while (rs.next()) {
				beze = new Bezero(rs.getString(1), rs.getString(2),
						rs.getString(3), rs.getString(4), rs.getInt(5),
						rs.getString(6));
				lista.add(beze);
			}
		} else if (filtroa == 3) // abizenak
		{
			String query = "select b.bezeroId,b.izena,b.abizenak,b.helbidea,b.telefonoa,a.deskripzioa FROM BezeroAseguratua b, AseguruTipoa a WHERE b.aseguruTipoKodigoa = a.kodigoa AND b.abizenak='"
					+ bila + "'";
			ResultSet rs = dbk.executeSQL(query);
			Bezero beze;
			while (rs.next()) {
				beze = new Bezero(rs.getString(1), rs.getString(2),
						rs.getString(3), rs.getString(4), rs.getInt(5),
						rs.getString(6));
				lista.add(beze);
			}
		} else if (filtroa == 4) // helb
		{
			String query = "select b.bezeroId,b.izena,b.abizenak,b.helbidea,b.telefonoa,a.deskripzioa FROM BezeroAseguratua b, AseguruTipoa a WHERE b.aseguruTipoKodigoa = a.kodigoa AND b.helbidea='"
					+ bila + "'";
			ResultSet rs = dbk.executeSQL(query);
			Bezero beze;
			while (rs.next()) {
				beze = new Bezero(rs.getString(1), rs.getString(2),
						rs.getString(3), rs.getString(4), rs.getInt(5),
						rs.getString(6));
				lista.add(beze);
			}
		} else if (filtroa == 5) // teleonoa
		{
			String query = "select b.bezeroId,b.izena,b.abizenak,b.helbidea,b.telefonoa,a.deskripzioa FROM BezeroAseguratua b, AseguruTipoa a WHERE b.aseguruTipoKodigoa = a.kodigoa AND b.telefonoa="
					+ bila + " ";
			ResultSet rs = dbk.executeSQL(query);
			Bezero beze;
			while (rs.next()) {
				beze = new Bezero(rs.getString(1), rs.getString(2),
						rs.getString(3), rs.getString(4), rs.getInt(5),
						rs.getString(6));
				lista.add(beze);
			}
		} else if (filtroa == 6) // asegurua
		{
			String query = "select b.bezeroId,b.izena,b.abizenak,b.helbidea,b.telefonoa,a.deskripzioa FROM BezeroAseguratua b, AseguruTipoa a WHERE b.aseguruTipoKodigoa = a.kodigoa AND a.deskripzioa='"
					+ bila + "'";
			ResultSet rs = dbk.executeSQL(query);
			Bezero beze;
			while (rs.next()) {
				beze = new Bezero(rs.getString(1), rs.getString(2),
						rs.getString(3), rs.getString(4), rs.getInt(5),
						rs.getString(6));
				lista.add(beze);
			}
		}

		return lista;
	}

	private String getTailerraIzena(int idTailerra) throws SQLException {
		DatuBasea dbk = DatuBasea.getInstance();
		String query = "SELECT tailerIzena from Tailerra where idTailerra = "
				+ idTailerra;
		ResultSet rs = dbk.executeSQL(query);
		String izena = "";
		if (rs.next()) {
			izena = rs.getString(1);
		}
		rs.close();

		return izena;
	}

	private String getPerituIzena(String idPeritua) throws SQLException {
		DatuBasea dbk = DatuBasea.getInstance();
		String query = "SELECT izena from Peritua where idPeritua = '"
				+ idPeritua + "'";
		ResultSet rs = dbk.executeSQL(query);
		String izena = "";
		if (rs.next()) {
			izena = rs.getString(1);
		}
		rs.close();

		return izena;
	}

	private String getAbokatuIzena(String idAbokatua) throws SQLException {
		DatuBasea dbk = DatuBasea.getInstance();
		String query = "SELECT izena from Abokatua where idAbokatua = '"
				+ idAbokatua + "'";
		ResultSet rs = dbk.executeSQL(query);
		String izena = "";
		if (rs.next()) {
			izena = rs.getString(1);
		}
		rs.close();

		return izena;
	}

	public List<Istripua> listatuIstripuak() throws SQLException {
		DatuBasea dbk = DatuBasea.getInstance();
		String query = "SELECT idIstripua,idTailerra,idPeritua,idAbokatua,oharrak,helbidea,izenaHiria FROM Istripua b, Hiria h WHERE "
				+ "b.idHiria=h.idHiria";
		ResultSet rs = dbk.executeSQL(query);
		Istripua is;
		List<Istripua> lista = new ArrayList<Istripua>();
		while (rs.next()) {
			int idIstripua = rs.getInt(1);
			int idTailerra = rs.getInt(2);
			String idPeritua = rs.getString(3);
			String idAbokatua = rs.getString(4);
			String oharrak = rs.getString(5);
			String helbidea = rs.getString(6);
			String hiria = rs.getString(7);

			is = new Istripua(idIstripua, idTailerra == 0 ? ""
					: getTailerraIzena(idTailerra), idPeritua == null ? ""
					: getPerituIzena(idPeritua), idAbokatua == null ? ""
					: getAbokatuIzena(idAbokatua), oharrak, helbidea, hiria);
			lista.add(is);
		}
		rs.close();

		return lista;
	}

	public List<Istripua> bilatuIstripuak(int filtroa, String bila)
			throws SQLException {
		DatuBasea dbk = DatuBasea.getInstance();
		List<Istripua> lista = new ArrayList<Istripua>();
		if (filtroa == 1) {// idIstripua

			String query = "SELECT idIstripua,idTailerra,idPeritua,idAbokatua,oharrak,helbidea,izenaHiria FROM Istripua b, Hiria h WHERE "
					+ "b.idHiria=h.idHiria and b.idIstripua='" + bila + "'";
			ResultSet rs = dbk.executeSQL(query);
			Istripua beze;
			while (rs.next()) {
				int idIstripua = rs.getInt(1);
				int idTailerra = rs.getInt(2);
				String idPeritua = rs.getString(3);
				String idAbokatua = rs.getString(4);
				String oharrak = rs.getString(5);
				String helbidea = rs.getString(6);
				String hiria = rs.getString(7);

				beze = new Istripua(idIstripua, idTailerra == 0 ? ""
						: getTailerraIzena(idTailerra), idPeritua == null ? ""
						: getPerituIzena(idPeritua), idAbokatua == null ? ""
						: getAbokatuIzena(idAbokatua), oharrak, helbidea, hiria);
				lista.add(beze);
			}
		} else if (filtroa == 2) // tailerra
		{
			String query = "SELECT idIstripua,b.idTailerra,idPeritua,idAbokatua,oharrak,b.helbidea,izenaHiria FROM Istripua b,Tailerra t, Hiria h WHERE "
					+ "b.idHiria=h.idHiria and b.idTailerra= t.idTailerra and t.tailerIzena='"
					+ bila + "'";
			ResultSet rs = dbk.executeSQL(query);
			Istripua beze;
			while (rs.next()) {
				int idIstripua = rs.getInt(1);
				int idTailerra = rs.getInt(2);
				String idPeritua = rs.getString(3);
				String idAbokatua = rs.getString(4);
				String oharrak = rs.getString(5);
				String helbidea = rs.getString(6);
				String hiria = rs.getString(7);

				beze = new Istripua(idIstripua, getTailerraIzena(idTailerra),
						idPeritua == null ? "" : getPerituIzena(idPeritua),
						idAbokatua == null ? "" : getAbokatuIzena(idAbokatua),
						oharrak, helbidea, hiria);
				lista.add(beze);

			}
		} else if (filtroa == 3) // Peritua
		{
			String query = "SELECT idIstripua,b.idTailerra,b.idPeritua,idAbokatua,oharrak,b.helbidea,izenaHiria FROM Istripua b,Peritua t, Hiria h WHERE "
					+ "b.idHiria=h.idHiria and t.idPeritua= b.idPeritua and t.izena='"
					+ bila + "'";
			ResultSet rs = dbk.executeSQL(query);
			Istripua beze;
			while (rs.next()) {
				int idIstripua = rs.getInt(1);
				int idTailerra = rs.getInt(2);
				String idPeritua = rs.getString(3);
				String idAbokatua = rs.getString(4);
				String oharrak = rs.getString(5);
				String helbidea = rs.getString(6);
				String hiria = rs.getString(7);

				beze = new Istripua(idIstripua, idTailerra == 0 ? ""
						: getTailerraIzena(idTailerra), idPeritua == null ? ""
						: getPerituIzena(idPeritua), idAbokatua == null ? ""
						: getAbokatuIzena(idAbokatua), oharrak, helbidea, hiria);
				lista.add(beze);
			}

		} else if (filtroa == 4) // abokatua
		{
			String query = "SELECT idIstripua,idTailerra,idPeritua,b.idAbokatua,oharrak,b.helbidea,izenaHiria FROM Istripua b,Abokatua t, Hiria h WHERE "
					+ "b.idHiria=h.idHiria and t.idAbokatua=b.idAbokatua and t.izena='"
					+ bila + "'";
			ResultSet rs = dbk.executeSQL(query);
			Istripua beze;
			while (rs.next()) {
				int idIstripua = rs.getInt(1);
				int idTailerra = rs.getInt(2);
				String idPeritua = rs.getString(3);
				String idAbokatua = rs.getString(4);
				String oharrak = rs.getString(5);
				String helbidea = rs.getString(6);
				String hiria = rs.getString(7);

				beze = new Istripua(idIstripua, idTailerra == 0 ? ""
						: getTailerraIzena(idTailerra), idPeritua == null ? ""
						: getPerituIzena(idPeritua), idAbokatua == null ? ""
						: getAbokatuIzena(idAbokatua), oharrak, helbidea, hiria);
				lista.add(beze);
			}
		} else if (filtroa == 5) // oharrak
		{
			String query = "SELECT idIstripua,idTailerra,idPeritua,idAbokatua,b.oharrak,helbidea,izenaHiria FROM Istripua b, Hiria h WHERE "
					+ "b.idHiria=h.idHiria and b.oharrak='" + bila + "'";
			ResultSet rs = dbk.executeSQL(query);
			Istripua beze;
			while (rs.next()) {
				int idIstripua = rs.getInt(1);
				int idTailerra = rs.getInt(2);
				String idPeritua = rs.getString(3);
				String idAbokatua = rs.getString(4);
				String oharrak = rs.getString(5);
				String helbidea = rs.getString(6);
				String hiria = rs.getString(7);

				beze = new Istripua(idIstripua, idTailerra == 0 ? ""
						: getTailerraIzena(idTailerra), idPeritua == null ? ""
						: getPerituIzena(idPeritua), idAbokatua == null ? ""
						: getAbokatuIzena(idAbokatua), oharrak, helbidea, hiria);
				lista.add(beze);
			}
		} else if (filtroa == 6) // helbidea
		{
			String query = "SELECT idIstripua,idTailerra,idPeritua,idAbokatua,oharrak,b.helbidea,izenaHiria FROM Istripua b, Hiria h WHERE "
					+ "b.idHiria=h.idHiria and b.helbidea='" + bila + "'";
			ResultSet rs = dbk.executeSQL(query);
			Istripua beze;
			while (rs.next()) {
				int idIstripua = rs.getInt(1);
				int idTailerra = rs.getInt(2);
				String idPeritua = rs.getString(3);
				String idAbokatua = rs.getString(4);
				String oharrak = rs.getString(5);
				String helbidea = rs.getString(6);
				String hiria = rs.getString(7);

				beze = new Istripua(idIstripua, idTailerra == 0 ? ""
						: getTailerraIzena(idTailerra), idPeritua == null ? ""
						: getPerituIzena(idPeritua), idAbokatua == null ? ""
						: getAbokatuIzena(idAbokatua), oharrak, helbidea, hiria);
				lista.add(beze);
			}
		} else if (filtroa == 7) // bidaiaria
		{
			String query = "SELECT idIstripua,idTailerra,idPeritua,idAbokatua,oharrak,helbidea,h.izenaHiria FROM Istripua b, Hiria h WHERE "
					+ "b.idHiria=h.idHiria and h.izenaHiria='" + bila + "'";
			ResultSet rs = dbk.executeSQL(query);
			Istripua beze;
			while (rs.next()) {
				int idIstripua = rs.getInt(1);
				int idTailerra = rs.getInt(2);
				String idPeritua = rs.getString(3);
				String idAbokatua = rs.getString(4);
				String oharrak = rs.getString(5);
				String helbidea = rs.getString(6);
				String hiria = rs.getString(7);

				beze = new Istripua(idIstripua, idTailerra == 0 ? ""
						: getTailerraIzena(idTailerra), idPeritua == null ? ""
						: getPerituIzena(idPeritua), idAbokatua == null ? ""
						: getAbokatuIzena(idAbokatua), oharrak, helbidea, hiria);
				lista.add(beze);
			}
		}

		return lista;
	}

	public List<Abokatu> listatuAbokatuak() throws SQLException {
		DatuBasea dbk = DatuBasea.getInstance();
		String query = "select b.idAbokatua,b.izena,b.abizenak,b.telefonoa,b.helbidea,h.izenaHiria FROM Abokatua  b, Hiria  h WHERE b.idHiria=h.idHiria";
		ResultSet rs = dbk.executeSQL(query);
		Abokatu is;
		List<Abokatu> lista = new ArrayList<Abokatu>();
		while (rs.next()) {
			is = new Abokatu(rs.getString(1), rs.getString(2), rs.getString(3),
					rs.getInt(4), rs.getString(5), rs.getString(6));
			lista.add(is);
		}
		rs.close();

		return lista;
	}

	public List<Abokatu> bilatuAbokatuakk(int filtroa, String bila)
			throws SQLException {
		DatuBasea dbk = DatuBasea.getInstance();
		List<Abokatu> lista = new ArrayList<Abokatu>();
		if (filtroa == 1) {// NAN

			String query = "select b.idAbokatua,b.izena,b.abizenak,b.telefonoa,b.helbidea,h.izenaHiria FROM Abokatua  b, Hiria  h WHERE b.idHiria=h.idHiria AND b.idAbokatua='"
					+ bila + "'";
			ResultSet rs = dbk.executeSQL(query);
			Abokatu beze;
			while (rs.next()) {
				beze = new Abokatu(rs.getString(1), rs.getString(2),
						rs.getString(3), rs.getInt(4), rs.getString(5),
						rs.getString(6));
				lista.add(beze);
			}
		} else if (filtroa == 2) // izena
		{
			String query = "select b.idAbokatua,b.izena,b.abizenak,b.telefonoa,b.helbidea,h.izenaHiria FROM Abokatua  b, Hiria  h WHERE b.idHiria=h.idHiria AND b.izena='"
					+ bila + "'";
			ResultSet rs = dbk.executeSQL(query);
			Abokatu beze;
			while (rs.next()) {
				beze = new Abokatu(rs.getString(1), rs.getString(2),
						rs.getString(3), rs.getInt(4), rs.getString(5),
						rs.getString(6));
				lista.add(beze);
			}
		} else if (filtroa == 3) // abizenak
		{
			String query = "select b.idAbokatua,b.izena,b.abizenak,b.telefonoa,b.helbidea,h.izenaHiria FROM Abokatua  b, Hiria  h WHERE b.idHiria=h.idHiria AND b.abizenak='"
					+ bila + "'";
			ResultSet rs = dbk.executeSQL(query);
			Abokatu beze;
			while (rs.next()) {
				beze = new Abokatu(rs.getString(1), rs.getString(2),
						rs.getString(3), rs.getInt(4), rs.getString(5),
						rs.getString(6));
				lista.add(beze);
			}
		} else if (filtroa == 5) // helb
		{
			String query = "select b.idAbokatua,b.izena,b.abizenak,b.telefonoa,b.helbidea,h.izenaHiria FROM Abokatua  b, Hiria  h WHERE b.idHiria=h.idHiria AND b.helbidea='"
					+ bila + "'";
			ResultSet rs = dbk.executeSQL(query);
			Abokatu beze;
			while (rs.next()) {
				beze = new Abokatu(rs.getString(1), rs.getString(2),
						rs.getString(3), rs.getInt(4), rs.getString(5),
						rs.getString(6));
				lista.add(beze);
			}
		} else if (filtroa == 4) // teleonoa
		{
			String query = "select b.idAbokatua,b.izena,b.abizenak,b.telefonoa,b.helbidea,h.izenaHiria FROM Abokatua  b, Hiria  h WHERE b.idHiria=h.idHiria AND b.telefonoa='"
					+ bila + "'";
			ResultSet rs = dbk.executeSQL(query);
			Abokatu beze;
			while (rs.next()) {
				beze = new Abokatu(rs.getString(1), rs.getString(2),
						rs.getString(3), rs.getInt(4), rs.getString(5),
						rs.getString(6));
				lista.add(beze);
			}
		} else if (filtroa == 6) // idHiria
		{
			String query = "select b.idAbokatua,b.izena,b.abizenak,b.telefonoa,b.helbidea,h.izenaHiria FROM Abokatua  b, Hiria  h WHERE b.idHiria=h.idHiria AND h.izenaHiria='"
					+ bila + "'";
			ResultSet rs = dbk.executeSQL(query);
			Abokatu beze;
			while (rs.next()) {
				beze = new Abokatu(rs.getString(1), rs.getString(2),
						rs.getString(3), rs.getInt(4), rs.getString(5),
						rs.getString(6));
				lista.add(beze);
			}
		}

		return lista;
	}

	public List<Tailer> listatuTailerrak() throws SQLException {
		DatuBasea dbk = DatuBasea.getInstance();
		String query = "SELECT b.idTailerra,b.tailerIzena,b.arduradunIzena,b.helbidea,b.telefonoa,h.izenaHiria FROM Tailerra  b, Hiria  h where b.idHiria=h.idHiria";
		ResultSet rs = dbk.executeSQL(query);
		Tailer is;
		List<Tailer> lista = new ArrayList<Tailer>();
		while (rs.next()) {
			is = new Tailer(rs.getInt(1), rs.getString(2), rs.getString(3),
					rs.getString(4), rs.getInt(5), rs.getString("izenaHiria"));
			lista.add(is);
		}
		rs.close();

		return lista;
	}

	public List<Tailer> bilatuTailerrak(int filtroa, String bila)
			throws SQLException {
		DatuBasea dbk = DatuBasea.getInstance();
		List<Tailer> lista = new ArrayList<Tailer>();
		if (filtroa == 1) {// id

			String query = "select b.idTailerra,b.tailerIzena,b.arduradunIzena,b.helbidea,b.telefonoa,h.izenaHiria FROM Tailerra  b, Hiria  h where b.idHiria=h.idHiria and b.idTailerra="
					+ bila + "";
			ResultSet rs = dbk.executeSQL(query);
			Tailer beze;
			while (rs.next()) {
				beze = new Tailer(rs.getInt(1), rs.getString(2),
						rs.getString(3), rs.getString(4), rs.getInt(5),
						rs.getString(6));
				lista.add(beze);
			}
		} else if (filtroa == 2) // izena
		{
			String query = "select * FROM Tailerra b, Hiria a WHERE b.idHiria = a.idHiria AND b.tailerIzena='"
					+ bila + "'";
			ResultSet rs = dbk.executeSQL(query);
			Tailer beze;
			while (rs.next()) {
				beze = new Tailer(rs.getInt("idTailerra"),
						rs.getString("tailerIzena"),
						rs.getString("arduradunIzena"),
						rs.getString("helbidea"), rs.getInt("telefonoa"),
						rs.getString("izenaHiria"));
				lista.add(beze);
			}
		} else if (filtroa == 3) // arduraduna
		{
			String query = "select * FROM Tailerra b, Hiria a WHERE b.idHiria = a.idHiria AND b.arduradunIzena='"
					+ bila + "'";
			ResultSet rs = dbk.executeSQL(query);
			Tailer beze;
			while (rs.next()) {
				beze = new Tailer(rs.getInt("idTailerra"),
						rs.getString("tailerIzena"),
						rs.getString("arduradunIzena"),
						rs.getString("helbidea"), rs.getInt("telefonoa"),
						rs.getString("izenaHiria"));
				lista.add(beze);
			}
		} else if (filtroa == 4) // helb
		{
			String query = "select * FROM Tailerra b, Hiria a WHERE b.idHiria = a.idHiria AND b.helbidea='"
					+ bila + "'";
			ResultSet rs = dbk.executeSQL(query);
			Tailer beze;
			while (rs.next()) {
				beze = new Tailer(rs.getInt("idTailerra"),
						rs.getString("tailerIzena"),
						rs.getString("arduradunIzena"),
						rs.getString("helbidea"), rs.getInt("telefonoa"),
						rs.getString("izenaHiria"));
				lista.add(beze);
			}
		} else if (filtroa == 5) // teleonoa
		{
			String query = "select * FROM Tailerra b, Hiria a WHERE b.idHiria = a.idHiria AND b.telefonoa="
					+ bila + "";
			ResultSet rs = dbk.executeSQL(query);
			Tailer beze;
			while (rs.next()) {
				beze = new Tailer(rs.getInt("idTailerra"),
						rs.getString("tailerIzena"),
						rs.getString("arduradunIzena"),
						rs.getString("helbidea"), rs.getInt("telefonoa"),
						rs.getString("izenaHiria"));
				lista.add(beze);
			}
		} else if (filtroa == 6) // hiri
		{
			String query = "select * FROM Tailerra b, Hiria a WHERE b.idHiria = a.idHiria AND a.izenaHiria='"
					+ bila + "'";
			ResultSet rs = dbk.executeSQL(query);
			Tailer beze;
			while (rs.next()) {
				beze = new Tailer(rs.getInt("idTailerra"),
						rs.getString("tailerIzena"),
						rs.getString("arduradunIzena"),
						rs.getString("helbidea"), rs.getInt("telefonoa"),
						rs.getString("izenaHiria"));
				lista.add(beze);
			}
		}

		return lista;
	}

	public List<MedikuKlinika> listatuMedikuKlinika() throws SQLException {
		DatuBasea dbk = DatuBasea.getInstance();
		String query = "select h.nan,k.izenaKlinika,b.hasData,b.BukData FROM MedikuKlinika b,Klinika k, Medikua  h WHERE b.medikuZenb=h.medikuZenb and b.idKlinika=k.idKlinika";
		ResultSet rs = dbk.executeSQL(query);
		MedikuKlinika is;
		List<MedikuKlinika> lista = new ArrayList<MedikuKlinika>();
		while (rs.next()) {
			is = new MedikuKlinika(rs.getString(1), rs.getString(2),
					rs.getLong(3), rs.getLong(4));
			lista.add(is);
		}
		rs.close();
		return lista;
	}

	public List<Inbolukratu> listatuInbolukratuak() throws SQLException {
		DatuBasea dbk = DatuBasea.getInstance();
		String query = "select b.idIstripua,b.bidaiariNif,b.matrikula FROM Inbolukratuak b";
		ResultSet rs = dbk.executeSQL(query);
		Inbolukratu is;
		List<Inbolukratu> lista = new ArrayList<Inbolukratu>();
		while (rs.next()) {
			is = new Inbolukratu(rs.getInt(1), rs.getString(2), rs.getString(3));
			lista.add(is);
		}
		rs.close();
		return lista;
	}

	public List<BalorazioKotxeIstripu> listatuBalorazioak() throws SQLException {
		DatuBasea dbk = DatuBasea.getInstance();
		String query = "select a.deskripzioa,b.matrikula,b.idIstripua,b.behaketak,b.balorea FROM BalorazioKotxeIstripu b, Eremu a WHERE b.idEremu = a.idEremu";
		ResultSet rs = dbk.executeSQL(query);
		BalorazioKotxeIstripu beze;
		List<BalorazioKotxeIstripu> lista = new ArrayList<BalorazioKotxeIstripu>();
		while (rs.next()) {
			beze = new BalorazioKotxeIstripu(rs.getString(1), rs.getString(2),
					rs.getInt(3), rs.getString(4), rs.getFloat(5));
			lista.add(beze);
		}
		rs.close();

		return lista;
	}

	public List<Diagnostiko> listatuDiagnostikoak() throws SQLException {
		DatuBasea dbk = DatuBasea.getInstance();
		String query = "select d.idDiagnostikoa,m.izena,d.data,d.lesioak,d.oharrak,d.tratamendua,d.bidaiariNif FROM"
				+ " Diagnostikoa d, Medikua m,Bidaiaria b WHERE d.medikuZenb = m.medikuZenb and d.bidaiariNif=b.bidaiariNif";
		ResultSet rs = dbk.executeSQL(query);
		Diagnostiko beze;
		List<Diagnostiko> lista = new ArrayList<Diagnostiko>();
		while (rs.next()) {
			beze = new Diagnostiko(rs.getInt(1), rs.getString(2),
					rs.getLong(3), rs.getString(4), rs.getString(5),
					rs.getString(6), rs.getString(7));
			lista.add(beze);
		}
		rs.close();

		return lista;
	}

	public List<Peritu> listatuPerituak() throws SQLException {
		DatuBasea dbk = DatuBasea.getInstance();
		String query = "select b.idPeritua,b.izena,b.abizenak,b.telefonoa,a.izenaHiria FROM Peritua b, Hiria a WHERE b.idHiria = a.idHiria";
		ResultSet rs = dbk.executeSQL(query);
		Peritu beze;
		List<Peritu> lista = new ArrayList<Peritu>();
		while (rs.next()) {
			beze = new Peritu(rs.getString(1), rs.getString(2),
					rs.getString(3), rs.getInt(4), rs.getString(5));
			lista.add(beze);
		}
		rs.close();

		return lista;
	}

	public List<Peritu> bilatuPerituak(int filtroa, String bila)
			throws SQLException {
		DatuBasea dbk = DatuBasea.getInstance();
		List<Peritu> lista = new ArrayList<Peritu>();
		if (filtroa == 1) {// NAN

			String query = "select b.idPeritua,b.izena,b.abizenak,b.telefonoa,a.izenaHiria FROM Peritua b, Hiria a WHERE b.idHiria = a.idHiria AND b.idPeritua='"
					+ bila + "'";
			ResultSet rs = dbk.executeSQL(query);
			Peritu beze;
			while (rs.next()) {
				beze = new Peritu(rs.getString(1), rs.getString(2),
						rs.getString(3), rs.getInt(4), rs.getString(5));
				lista.add(beze);
			}
		} else if (filtroa == 2) // izena
		{
			String query = "select b.idPeritua,b.izena,b.abizenak,b.telefonoa,a.izenaHiria FROM Peritua b, Hiria a WHERE b.idHiria = a.idHiria AND b.izena='"
					+ bila + "'";
			ResultSet rs = dbk.executeSQL(query);
			Peritu beze;
			while (rs.next()) {
				beze = new Peritu(rs.getString(1), rs.getString(2),
						rs.getString(3), rs.getInt(4), rs.getString(5));
				;
				lista.add(beze);
			}
		} else if (filtroa == 3) // abizenak
		{
			String query = "select b.idPeritua,b.izena,b.abizenak,b.telefonoa,a.izenaHiria FROM Peritua b, Hiria a WHERE b.idHiria = a.idHiria AND b.abizenak='"
					+ bila + "'";
			ResultSet rs = dbk.executeSQL(query);
			Peritu beze;
			while (rs.next()) {
				beze = new Peritu(rs.getString(1), rs.getString(2),
						rs.getString(3), rs.getInt(4), rs.getString(5));
				lista.add(beze);
			}

		} else if (filtroa == 4) // telefonoa
		{
			String query = "select b.idPeritua,b.izena,b.abizenak,b.telefonoa,a.izenaHiria FROM Peritua b, Hiria a WHERE b.idHiria = a.idHiria AND b.telefonoa='"
					+ bila + "'";
			ResultSet rs = dbk.executeSQL(query);
			Peritu beze;
			while (rs.next()) {
				beze = new Peritu(rs.getString(1), rs.getString(2),
						rs.getString(3), rs.getInt(4), rs.getString(5));
				lista.add(beze);
			}
		} else if (filtroa == 5) // idHiria
		{
			String query = "select b.idPeritua,b.izena,b.abizenak,b.telefonoa,a.izenaHiria FROM Peritua b, Hiria a WHERE b.idHiria = a.idHiria AND a.izenaHiria='"
					+ bila + "'";
			ResultSet rs = dbk.executeSQL(query);
			Peritu beze;
			while (rs.next()) {
				beze = new Peritu(rs.getString(1), rs.getString(2),
						rs.getString(3), rs.getInt(4), rs.getString(5));
				lista.add(beze);
			}
		}

		return lista;
	}

	public List<Inbolukratu> bilatuInbolukratuak(int filtroa, String bila)
			throws SQLException {
		DatuBasea dbk = DatuBasea.getInstance();
		List<Inbolukratu> lista = new ArrayList<Inbolukratu>();
		if (filtroa == 1) {// Istripua

			String query = "select b.idIstripua,b.bidaiariNif,b.matrikula FROM Inbolukratuak b WHERE b.idIstripua='"
					+ bila + "'";
			ResultSet rs = dbk.executeSQL(query);
			Inbolukratu beze;
			while (rs.next()) {
				beze = new Inbolukratu(rs.getInt(1), rs.getString(2),
						rs.getString(3));
				lista.add(beze);
			}
		} else if (filtroa == 2) // Bidaiariak
		{
			String query = "select b.idIstripua,b.bidaiariNif,b.matrikula FROM Inbolukratuak b WHERE b.bidaiariNif='"
					+ bila + "'";
			ResultSet rs = dbk.executeSQL(query);
			Inbolukratu beze;
			while (rs.next()) {
				beze = new Inbolukratu(rs.getInt(1), rs.getString(2),
						rs.getString(3));
				lista.add(beze);

			}
		} else if (filtroa == 3) // Matrikulak
		{
			String query = "select b.idIstripua,b.bidaiariNif,b.matrikula FROM Inbolukratuak b WHERE b.matrikula='"
					+ bila + "'";
			ResultSet rs = dbk.executeSQL(query);
			Inbolukratu beze;
			while (rs.next()) {
				beze = new Inbolukratu(rs.getInt(1), rs.getString(2),
						rs.getString(3));
				lista.add(beze);

			}
		}

		return lista;
	}

	public List<Diagnostiko> bilatuDiagnostikoak(int filtroa, String bila)
			throws SQLException {
		DatuBasea dbk = DatuBasea.getInstance();
		List<Diagnostiko> lista = new ArrayList<Diagnostiko>();
		if (filtroa == 1) {// idDiagnostikoa

			String query = "select d.idDiagnostikoa,m.izena,d.data,d.lesioak,d.oharrak,d.tratamendua,d.bidaiariNif FROM"
					+ " Diagnostikoa d, Medikua m,Bidaiaria b WHERE d.medikuZenb = m.medikuZenb and d.bidaiariNif=b.bidaiariNif AND d.idDiagnostikoa='"
					+ bila + "'";
			ResultSet rs = dbk.executeSQL(query);
			Diagnostiko beze;
			while (rs.next()) {
				beze = new Diagnostiko(rs.getInt(1), rs.getString(2),
						rs.getLong(3), rs.getString(4), rs.getString(5),
						rs.getString(6), rs.getString(7));
				lista.add(beze);
			}
		} else if (filtroa == 2) // medikua
		{
			String query = "select d.idDiagnostikoa,m.izena,d.data,d.lesioak,d.oharrak,d.tratamendua,d.bidaiariNif FROM"
					+ " Diagnostikoa d, Medikua m,Bidaiaria b WHERE d.medikuZenb = m.medikuZenb and d.bidaiariNif=b.bidaiariNif AND m.izena='"
					+ bila + "'";
			ResultSet rs = dbk.executeSQL(query);
			Diagnostiko beze;
			while (rs.next()) {
				beze = new Diagnostiko(rs.getInt(1), rs.getString(2),
						rs.getLong(3), rs.getString(4), rs.getString(5),
						rs.getString(6), rs.getString(7));
				lista.add(beze);
			}
		} else if (filtroa == 3) // data
		{
			SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy");
			try {
				Date data = sdf.parse(bila);
				long data1 = data.getTime();
				@SuppressWarnings("deprecation")
				long data2 = new Date(data.getYear(), data.getMonth(),
						data.getDate() + 1).getTime() - 1;
				String query = "select d.idDiagnostikoa,m.izena,d.data,d.lesioak,d.oharrak,d.tratamendua,d.bidaiariNif FROM"
						+ " Diagnostikoa d, Medikua m,Bidaiaria b WHERE d.medikuZenb = m.medikuZenb and d.bidaiariNif=b.bidaiariNif AND d.data >= "
						+ data1 + " and d.data <= " + data2;
				ResultSet rs = dbk.executeSQL(query);
				Diagnostiko beze;
				while (rs.next()) {
					beze = new Diagnostiko(rs.getInt(1), rs.getString(2),
							rs.getLong(3), rs.getString(4), rs.getString(5),
							rs.getString(6), rs.getString(7));
					lista.add(beze);
				}
			} catch (ParseException e) {
				e.printStackTrace();
			}

		} else if (filtroa == 4) // lesioak
		{
			String query = "select d.idDiagnostikoa,m.izena,d.data,d.lesioak,d.oharrak,d.tratamendua,d.bidaiariNif FROM"
					+ " Diagnostikoa d, Medikua m,Bidaiaria b WHERE d.medikuZenb = m.medikuZenb and d.bidaiariNif=b.bidaiariNif AND d.lesioak='"
					+ bila + "'";
			ResultSet rs = dbk.executeSQL(query);
			Diagnostiko beze;
			while (rs.next()) {
				beze = new Diagnostiko(rs.getInt(1), rs.getString(2),
						rs.getLong(3), rs.getString(4), rs.getString(5),
						rs.getString(6), rs.getString(7));
				lista.add(beze);
			}

		} else if (filtroa == 5) // oharrak
		{
			String query = "select d.idDiagnostikoa,m.izena,d.data,d.lesioak,d.oharrak,d.tratamendua,d.bidaiariNif FROM"
					+ " Diagnostikoa d, Medikua m,Bidaiaria b WHERE d.medikuZenb = m.medikuZenb and d.bidaiariNif=b.bidaiariNif AND d.oharrak='"
					+ bila + "'";
			ResultSet rs = dbk.executeSQL(query);
			Diagnostiko beze;
			while (rs.next()) {
				beze = new Diagnostiko(rs.getInt(1), rs.getString(2),
						rs.getLong(3), rs.getString(4), rs.getString(5),
						rs.getString(6), rs.getString(7));
				lista.add(beze);
			}
		} else if (filtroa == 6) // tratamendua
		{
			String query = "select d.idDiagnostikoa,m.izena,d.data,d.lesioak,d.oharrak,d.tratamendua,d.bidaiariNif FROM"
					+ " Diagnostikoa d, Medikua m,Bidaiaria b WHERE d.medikuZenb = m.medikuZenb and d.bidaiariNif=b.bidaiariNif AND d.tratamendua='"
					+ bila + "'";
			ResultSet rs = dbk.executeSQL(query);
			Diagnostiko beze;
			while (rs.next()) {
				beze = new Diagnostiko(rs.getInt(1), rs.getString(2),
						rs.getLong(3), rs.getString(4), rs.getString(5),
						rs.getString(6), rs.getString(7));
				lista.add(beze);
			}
		} else if (filtroa == 7) // bidaiaria
		{
			String query = "select d.idDiagnostikoa,m.izena,d.data,d.lesioak,d.oharrak,d.tratamendua,d.bidaiariNif FROM"
					+ " Diagnostikoa d, Medikua m,Bidaiaria b WHERE d.medikuZenb = m.medikuZenb and d.bidaiariNif=b.bidaiariNif AND b.bidaiariNif='"
					+ bila + "'";
			ResultSet rs = dbk.executeSQL(query);
			Diagnostiko beze;
			while (rs.next()) {
				beze = new Diagnostiko(rs.getInt(1), rs.getString(2),
						rs.getLong(3), rs.getString(4), rs.getString(5),
						rs.getString(6), rs.getString(7));
				lista.add(beze);
			}
		}

		return lista;
	}

	public List<MedikuKlinika> bilatuMedikuak(int filtroa, String bila)
			throws SQLException {
		DatuBasea dbk = DatuBasea.getInstance();
		List<MedikuKlinika> lista = new ArrayList<MedikuKlinika>();
		if (filtroa == 1) {// Medikua

			String query = "select h.nan,k.izenaKlinika,b.hasData,b.BukData FROM MedikuKlinika b,Klinika k, Medikua  h WHERE b.medikuZenb=h.medikuZenb and b.idKlinika=k.idKlinika and h.nan= '"
					+ bila + "' ";
			ResultSet rs = dbk.executeSQL(query);
			MedikuKlinika beze;
			while (rs.next()) {
				beze = new MedikuKlinika(rs.getString(1), rs.getString(2),
						rs.getLong(3), rs.getLong(4));
				lista.add(beze);
			}
		} else if (filtroa == 2) // klinikaizena
		{
			String query = "select h.nan,k.izenaKlinika,b.hasData,b.BukData FROM MedikuKlinika b,Klinika k, Medikua  h WHERE b.medikuZenb=h.medikuZenb and b.idKlinika=k.idKlinika and k.izenaKlinika='"
					+ bila + "'";
			ResultSet rs = dbk.executeSQL(query);
			MedikuKlinika beze;
			while (rs.next()) {
				beze = new MedikuKlinika(rs.getString(1), rs.getString(2),
						rs.getLong(3), rs.getLong(4));
				lista.add(beze);
			}
		} else if (filtroa == 3) // hasData
		{
			SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy");
			try {
				Date data = sdf.parse(bila);
				long data1 = data.getTime();
				@SuppressWarnings("deprecation")
				long data2 = new Date(data.getYear(), data.getMonth(),
						data.getDate() + 1).getTime() - 1;

				String query = "select h.nan,k.izenaKlinika,b.hasData,b.BukData FROM MedikuKlinika b,Klinika k, Medikua  h WHERE b.medikuZenb=h.medikuZenb and b.idKlinika=k.idKlinika AND "
						+ "b.hasData >= "
						+ data1
						+ " and b.hasData <= "
						+ data2;

				ResultSet rs = dbk.executeSQL(query);
				MedikuKlinika beze;
				while (rs.next()) {
					beze = new MedikuKlinika(rs.getString(1), rs.getString(2),
							rs.getLong(3), rs.getLong(4));
					lista.add(beze);
				}
			} catch (ParseException e) {
				e.printStackTrace();
			}

		} else if (filtroa == 4) // bukData
		{
			SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy");
			try {
				Date data = sdf.parse(bila);
				long data1 = data.getTime();
				@SuppressWarnings("deprecation")
				long data2 = new Date(data.getYear(), data.getMonth(),
						data.getDate() + 1).getTime() - 1;

				String query = "select h.nan,k.izenaKlinika,b.hasData,b.BukData FROM MedikuKlinika b,Klinika k, Medikua  h WHERE b.medikuZenb=h.medikuZenb and b.idKlinika=k.idKlinika and "
						+ "b.bukData >= "
						+ data1
						+ " and b.bukData <= "
						+ data2;
				ResultSet rs = dbk.executeSQL(query);
				MedikuKlinika beze;
				while (rs.next()) {
					beze = new MedikuKlinika(rs.getString("nan"),
							rs.getString("izenaKlinika"),
							rs.getLong("hasData"), rs.getLong("bukData"));
					lista.add(beze);
				}
			} catch (ParseException e) {
				e.printStackTrace();
			}
		}

		return lista;
	}

	public List<BalorazioKotxeIstripu> bilatuBalorazioak(int filtroa,
			String bila) throws SQLException {
		DatuBasea dbk = DatuBasea.getInstance();
		List<BalorazioKotxeIstripu> lista = new ArrayList<BalorazioKotxeIstripu>();
		if (filtroa == 1) {// idEremu

			String query = "select a.deskripzioa,b.matrikula,b.idIstripua,b.behaketak,b.balorea FROM BalorazioKotxeIstripu b, Eremu a WHERE b.idEremu = a.idEremu AND a.deskripzioa='"
					+ bila + "'";
			ResultSet rs = dbk.executeSQL(query);
			BalorazioKotxeIstripu beze;
			while (rs.next()) {
				beze = new BalorazioKotxeIstripu(rs.getString(1),
						rs.getString(2), rs.getInt(3), rs.getString(4),
						rs.getFloat(5));
				lista.add(beze);
			}
		} else if (filtroa == 2) // matrikula
		{
			String query = "select a.deskripzioa,b.matrikula,b.idIstripua,b.behaketak,b.balorea FROM BalorazioKotxeIstripu b, Eremu a WHERE b.idEremu = a.idEremu AND b.matrikula='"
					+ bila + "'";
			ResultSet rs = dbk.executeSQL(query);
			BalorazioKotxeIstripu beze;
			while (rs.next()) {
				beze = new BalorazioKotxeIstripu(rs.getString(1),
						rs.getString(2), rs.getInt(3), rs.getString(4),
						rs.getFloat(5));
				lista.add(beze);
			}
		} else if (filtroa == 3) // istripua
		{
			String query = "select a.deskripzioa,b.matrikula,b.idIstripua,b.behaketak,b.balorea FROM BalorazioKotxeIstripu b, Eremu a WHERE b.idEremu = a.idEremu AND b.idIstripua='"
					+ bila + "'";
			ResultSet rs = dbk.executeSQL(query);
			BalorazioKotxeIstripu beze;
			while (rs.next()) {
				beze = new BalorazioKotxeIstripu(rs.getString(1),
						rs.getString(2), rs.getInt(3), rs.getString(4),
						rs.getFloat(5));
				lista.add(beze);
			}

		} else if (filtroa == 4) // behaketak
		{
			String query = "select a.deskripzioa,b.matrikula,b.idIstripua,b.behaketak,b.balorea FROM BalorazioKotxeIstripu b, Eremu a WHERE b.idEremu = a.idEremu AND b.behaketak='"
					+ bila + "'";
			ResultSet rs = dbk.executeSQL(query);
			BalorazioKotxeIstripu beze;
			while (rs.next()) {
				beze = new BalorazioKotxeIstripu(rs.getString(1),
						rs.getString(2), rs.getInt(3), rs.getString(4),
						rs.getFloat(5));
				lista.add(beze);
			}
		} else if (filtroa == 5) // balorea
		{
			String query = "select a.deskripzioa,b.matrikula,b.idIstripua,b.behaketak,b.balorea FROM BalorazioKotxeIstripu b, Eremu a WHERE b.idEremu = a.idEremu AND b.balorea='"
					+ bila + "'";
			ResultSet rs = dbk.executeSQL(query);
			BalorazioKotxeIstripu beze;
			while (rs.next()) {
				beze = new BalorazioKotxeIstripu(rs.getString(1),
						rs.getString(2), rs.getInt(3), rs.getString(4),
						rs.getFloat(5));
				lista.add(beze);
			}

		}

		return lista;
	}

	public List<BezeroIbilgailu> bilatuKotxeak(int filtroa, String bila)
			throws SQLException {
		DatuBasea dbk = DatuBasea.getInstance();
		List<BezeroIbilgailu> lista = new ArrayList<BezeroIbilgailu>();
		if (filtroa == 1) {// matrikula

			String query = "select b.matrikula,b.bezeroId,b.urtea FROM bezeroibilgailuak b WHERE b.matrikula='"
					+ bila + "'";
			ResultSet rs = dbk.executeSQL(query);
			BezeroIbilgailu beze;
			while (rs.next()) {
				beze = new BezeroIbilgailu(rs.getString(1), rs.getString(2),
						rs.getLong(3));
				lista.add(beze);
			}
		} else if (filtroa == 2) // bezeroa
		{
			String query = "select b.matrikula,b.bezeroId,b.urtea FROM bezeroibilgailuak b WHERE b.bezeroId='"
					+ bila + "'";
			ResultSet rs = dbk.executeSQL(query);
			BezeroIbilgailu beze;

			while (rs.next()) {
				beze = new BezeroIbilgailu(rs.getString(1), rs.getString(2),
						rs.getLong(3));
				lista.add(beze);

			}
		} else if (filtroa == 3) // data
		{
			SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy");
			try {
				Date data = sdf.parse(bila);
				long data1 = data.getTime();
				@SuppressWarnings("deprecation")
				long data2 = new Date(data.getYear(), data.getMonth(),
						data.getDate() + 1).getTime() - 1;

				String query = "select b.matrikula,b.bezeroId,b.urtea FROM BezeroIbilgailuak b WHERE "
						+ "b.urtea >= " + data1 + " and b.urtea <= " + data2;

				ResultSet rs = dbk.executeSQL(query);
				BezeroIbilgailu beze;
				while (rs.next()) {
					beze = new BezeroIbilgailu(rs.getString(1),
							rs.getString(2), rs.getLong(3));
					lista.add(beze);
				}

			} catch (ParseException e) {
				e.printStackTrace();
			}

		}

		return lista;
	}
}
